package lig.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import lig.com.conn.JdbcUtil;
import lig.com.dto.IbnrErnDTO;
import lig.com.dto.IbnrErnDTOList;


public class IbnrErnDAO {
private static IbnrErnDAO instance = new IbnrErnDAO();
	

	public static IbnrErnDAO getInstnace() {
		return instance;
	}	

	public IbnrErnDTOList IbnrErnMonthYTD(Connection conn, int yyyy1, int yyyy2) throws SQLException {

		StringBuffer sb = new StringBuffer();
		
		IbnrErnDTOList IbnrErnDtoList = new IbnrErnDTOList();
		PreparedStatement pstmt = null;
		
		ResultSet rs = null;
		
		try {		
			
			sb.append("SELECT A.ACTDTE, A.ERN,B.IncLoss,B.IncExp ");
					sb.append("FROM( ");	
					sb.append("	SELECT  [ACTDTE],[WRT],[ERN] ");
					sb.append("	  FROM [test_actdte].[dbo].[netpremium3] ");
					sb.append("	  where left(ACTDTE,4) in ( "+yyyy1+","+yyyy2+") and CatNo in (1) and MorY = 'YTD' ");
					sb.append(")A  ");
					sb.append("left join ");
					sb.append("( ");
					sb.append("	SELECT  [ACTDTE],[IncLoss],[IncExp] ");
					sb.append("	  FROM [test_actdte].[dbo].[netpremium3] ");
					sb.append("	  where left(ACTDTE,4) in ( "+yyyy1+","+yyyy2+") and CatNo in (6) and MorY = 'YTD' ");
					sb.append(")B ON A.ACTDTE = B.ACTDTE ");
					sb.append("ORDER BY ACTDTE ");
			
			pstmt = conn.prepareStatement(sb.toString());
			
			
			rs = pstmt.executeQuery();

			while(rs.next()){
				IbnrErnDTO IbnrErnDto = new IbnrErnDTO();				
				IbnrErnDto.setActdte(rs.getInt("ACTDTE"));
				IbnrErnDto.setTotalErn(rs.getBigDecimal("ERN"));
				IbnrErnDto.setIbnrLoss(rs.getBigDecimal("IncLoss"));
				IbnrErnDto.setIbnrExp(rs.getBigDecimal("IncExp"));	
				//System.out.println("PrmDto.getActDate : "+PrmDto.getActDate());
				IbnrErnDtoList.getDtoList().add(IbnrErnDto);
			}
			
		}	
		catch(Exception e){
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs);
			JdbcUtil.close(pstmt);
		}
		return IbnrErnDtoList;
	
	}
}
